package com.itheima.store.dao.impl;

import java.sql.Date;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.itheima.store.dao.BookDao;
import com.itheima.store.domain.Book;
import com.itheima.store.domain.User;
import com.itheima.store.utils.JDBCUtils;
/**
 * 商品DAO的实现类
 * @author admin
 *
 */
public class BookDaoImpl implements BookDao {

	@Override
	public List<Book> findByHot() throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select * from book where isunder = ? and ishot = ? order by book_createTime desc limit ?";
		List<Book> list = queryRunner.query(sql, new BeanListHandler<Book>(Book.class),0,0,9);
		return list;
	}

	@Override
	public List<Book> findByNew() throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select * from book where isunder = ? order by book_createTime desc limit ?";
		List<Book> list = queryRunner.query(sql, new BeanListHandler<Book>(Book.class), 0,9);
		return list;
	}

	@Override
	public Integer findCountByBook_category_id(String book_category_id) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select count(*) from book where book_category_id = ? and isunder = ?";
		Long count = (Long) queryRunner.query(sql, new ScalarHandler(), book_category_id,0);
		return count.intValue();
	}

	@Override
	public List<Book> findPageByBook_category_id(String book_category_id, int begin, Integer pageSize) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select * from book where isunder = ? and book_category_id = ? order by book_createTime desc limit ?,?";
		List<Book> list = queryRunner.query(sql, new BeanListHandler<Book>(Book.class), 0,book_category_id,begin,pageSize);
		return list;
	}

	@Override
	public Book findByBook_id(String book_id) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select * from book where book_id = ?";
		Book book = queryRunner.query(sql, new BeanHandler<Book>(Book.class), book_id);
		return book;
	}

	@Override
	public Integer findcountAll() throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select count(*) from book where isunder=0 ";
		Long count = (Long) queryRunner.query(sql, new ScalarHandler());
		return count.intValue();
	}

	@Override
	public List<Book> finByPage(int begin, Integer pageSize) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select * from book where isunder = ? order by book_createTime desc limit ?,?";
		List<Book> list = queryRunner.query(sql, new BeanListHandler<Book>(Book.class), 0,begin,pageSize);
		return list;
	}

	@Override
	public void save(Book book) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "insert into book values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
		Object[] params = { book.getBook_id(), book.getBook_number(), book.getBook_name(), book.getBook_author(),
				book.getBook_image(), book.getBook_createTime(), book.getBook_introduce (), book.getBook_status(), 
				book.getIsunder(),book.getBook_category_id(),book.getCreatePerson_id(),book.getCeratePerson(),book.getUnfoCricum(),
				book.getInventory(),book.getIshot() };
		queryRunner.update(sql, params);
	}

	@Override
	public void update(Book book) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "update book set book_name = ?,book_number=?,book_image=?,book_author=?,book_introduce=?,ishot=?,inventory=?,book_category_id=?,book_status= ?,isunder=?,unfoCricum=? where book_id = ?";
		Object[] params = { book.getBook_name(), book.getBook_number(), book.getBook_image(),
				book.getBook_author(),book.getBook_introduce(),book.getIshot(),book.getInventory(),book.getBook_category_id(),
				 book.getBook_status(), book.getIsunder(),book.getUnfoCricum(),book.getBook_id()
				 };
		queryRunner.update(sql, params);
		
	}

	public void updateEdit(Book book) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "update book set book_name = ?,book_number=?,book_image=?,ishot=?,book_status= ?,isunder=? where book_id = ?";
		Object[] params = { book.getBook_name(), book.getBook_number(), book.getBook_image(),
				book.getIshot(), book.getBook_status(), book.getIsunder(),book.getBook_id()
				};
		queryRunner.update(sql, params);
	}
	
	@Override
	public List<Book> finPushDown(Integer isunder) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql="select * from book where isunder=? order by book_createTime";
		List<Book> list = queryRunner.query(sql, new BeanListHandler<Book>(Book.class), isunder);
		return list;
	}

	@Override
	public Integer findAllByPage(String search) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select count(*) from book where book_name like binary ? ";
		Long count = (Long) queryRunner.query(sql, new ScalarHandler(),"%"+search+"%");
		return count.intValue();
	}

	@Override
	public List<Book> findAllByPage(int begin, int pageSize,String search) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = null;
		List<Book> list;
		if(null ==search || "".equals(search)) {
			sql = "select * from book order by book_createTime limit ?,?";
			list = queryRunner.query(sql, new BeanListHandler<Book>(Book.class), begin,pageSize);
			return list;
		}
		else {
			
			 sql = "select * from book where 1=1 and book_name like binary ? order by book_createTime limit ?,?";
			 list = queryRunner.query(sql, new BeanListHandler<Book>(Book.class), "%"+search+"%",begin,pageSize);

		}
		return list;
		
	}

	@Override
	public void delete(String book_id) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "delete from book where book_id=? ";
		queryRunner.update(sql,book_id);
	}

	@Override
	public Integer findCountByBookName(String book_name) throws SQLException {
		// TODO Auto-generated method stub
				QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
				String sql = "select count(*) from book where isunder=0 and book_name like ?";
				Long count = (Long) queryRunner.query(sql, new ScalarHandler(),book_name);
				return count.intValue();
	}

	@Override
	public List<Book> findPageByBookName(String book_name, int begin, Integer pageSize) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql="select * from book where isunder=0 and book_name like ? order by book_createTime limit ?,?";
		List<Book> list = queryRunner.query(sql, new BeanListHandler<Book>(Book.class), book_name,begin,pageSize);
		return list;
	}

	
}
